﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace DbView
{
	public partial class LookupForm : Form
	{
		// number of lookups above which the filter box is activated.
		// ie. don't distract the user with controls when they are not really necessary
		private const int FilterThreshold = 6;
		// Max report fields to add when creating lookups from the Fk list
		// Keep it low as lookups may not be used. Individual lookups can be extended.
		private const int AutoLookupReportFieldMax = 4;

		private List<string> lookups = new List<string>();
		private string connectionName;
		private string driverName;
		DbViewDriver.ILookup ifLookup; 

		public LookupForm(DbViewDriver.ILookup lookupIface, string currentDriverName, string currentConnectionName)
		{
			InitializeComponent();
			this.ifLookup = lookupIface;
			this.driverName = currentDriverName;
			this.connectionName = currentConnectionName;

			this.Text = this.Text.Replace("[]", this.connectionName);
			RefreshFromRepository();
		}

		private void SetControlState()
		{
			// disable delete and modify if selected item is null
			bool enabledState = (this.listLookups.SelectedItem != null);
			this.btnDelete.Enabled = enabledState;
			this.btnModify.Enabled = enabledState;
			this.btnReset.Enabled = this.textFilter.Text.Length > 0;
		}

		private void RefreshFromRepository()
		{
			lookups.Clear();
			DbView.Resources.LookupInfo.Key key = MakePartialKey();  // no table/field required.
			DbView.Resources.LookupInfo.Key[] keys = DbView.Resources.LookupInfo.Instance.FindKeysForConnection(key);
			foreach (DbView.Resources.LookupInfo.Key k in keys)
			{
				lookups.Add(string.Format("{0}.{1}", k.Table, k.Field));
			}
			// don't bother the user with the filter until its worth it
			this.textFilter.Enabled = (this.lookups.Count > FilterThreshold); 
			
			OnSelectionChange();
			OnChangeText();  // calles setcontolstate
		}

		private void btnClose_Click(object sender, EventArgs e)
		{
			if (true) // make this a dirty flag one day
				DbView.Resources.LookupInfo.Instance.SaveConfig();
			this.Close();
		}

		private void btnReset_Click(object sender, EventArgs e)
		{
			this.textFilter.Text = "";
			OnChangeText();
		}

		private void textFilter_TextChanged(object sender, EventArgs e)
		{
			OnChangeText();
		}

		private void listLookups_SelectedIndexChanged(object sender, EventArgs e)
		{
			OnSelectionChange();
		}

		private void OnChangeText()
		{
			string selected = (this.listLookups.SelectedIndex >= 0) ? this.listLookups.SelectedItem.ToString() : "";
			this.listLookups.Items.Clear();
			string seek = this.textFilter.Text;
			foreach (string lookupKey in this.lookups)
			{
				if (matches(seek, lookupKey))
				{
					this.listLookups.Items.Add(lookupKey);
					if (lookupKey == selected)
						this.listLookups.SelectedItem = selected;
				}
			}
			if (this.listLookups.SelectedIndex == -1 && this.listLookups.Items.Count > 0)
				this.listLookups.SelectedIndex = 0;
			this.SetControlState();
		}

		private bool matches(string seek, string lookupKey)
		{
			// prefer wildmatch?
			return lookupKey.ToUpper().Contains(seek.ToUpper());
		}

		private void OnSelectionChange()
		{
			if (this.listLookups.SelectedItem != null)
			{
				string tf = this.listLookups.SelectedItem.ToString();
				string table, field;
				this.TryParseKey(tf, out table, out field);
				DbView.Resources.LookupInfo.Key key = MakeKey(table, field);
				DbView.Resources.LookupInfo.Data data = DbView.Resources.LookupInfo.Instance.FindData(key);
				string command = (data == null) ? "" : this.ifLookup.BuildLookupCommand(data.PkTable, data.ReportFields, data.PkField, "Lookup Value(s)");
				this.lblCommand.Text = command;
			}
			else if (this.listLookups.Items.Count == 0)
				Help();
		}

		private void btnModify_Click(object sender, EventArgs e)
		{
			ModifyCurrrentSelectedKey();
		}
		private void listLookups_MouseDoubleClick(object sender, MouseEventArgs e)
		{
			ModifyCurrrentSelectedKey();
		}

		private void ModifyCurrrentSelectedKey()
		{
			if (this.listLookups.SelectedItem != null)
			{
				string table, field;
				this.TryParseKey(this.listLookups.SelectedItem.ToString(), out table, out field);
				DbView.Resources.LookupInfo.Key key = this.MakeKey(table, field);
				DbView.Resources.LookupInfo.Data data = DbView.Resources.LookupInfo.Instance.FindData(key);
				LookupModel rec = LookupModel.DataTransfer(key, data);
				AddOrModifyLookup(rec);
			}
		}

		private void AddOrModifyLookup(LookupModel rec)
		{
			LookupEditController controller = new DbView.LookupEditController(ifLookup.GetSchemaInfo(), rec);
			LookupEditForm dlg = new DbView.LookupEditForm(controller);
			if (dlg.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
			{
				DbView.Resources.LookupInfo.Key key = this.MakeKey(rec.Table, rec.Field);
				DbView.Resources.LookupInfo.Data report = LookupModel.DataTransfer(rec, key);
				DbView.Resources.LookupInfo.Instance.AddLookup(key, report);
				RefreshFromRepository();
				// set the selected item
				this.listLookups.SelectedItem = string.Format("{0}.{1}", key.Table, key.Field);
			}
		}

		private void btnAdd_Click(object sender, EventArgs e)
		{
			LookupModel rec = new LookupModel();
			AddOrModifyLookup(rec);
		}

		private void btnDelete_Click(object sender, EventArgs e)
		{
			if (this.listLookups.SelectedItem != null)
			{
				string tf = this.listLookups.SelectedItem.ToString();
				string message = "Remove the lookup for " + tf + "?";
				DialogResult doit = MessageBox.Show(message, "Confirm deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
				if (doit == System.Windows.Forms.DialogResult.Yes)
				{
					string table, field;
					this.TryParseKey(tf, out table, out field);
					DbView.Resources.LookupInfo.Key key = this.MakeKey(table, field);
					DbView.Resources.LookupInfo.Instance.RemoveLookup(key);
					RefreshFromRepository();
				}
			}
		}
		
		private void btnAuto_Click(object sender, EventArgs e)
		{
			// DoAutoCreate();
			DoAutoCreateFast();
		}

		// Scheduled for removal. It is too slow on the big work database.
		private void DoAutoCreate()
		{
			// message 
			string message = "This will create lookups based on all the foreign keys that exist in the database. Any existing lookups will not be altered. " + 
							"Do you wish to proceed?";
			if (MessageBox.Show(this, message, "Create Foreign key lookups", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
				// no aborts
				return;
			System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
			sw.Start();
			// use interface to get Fk info
			DataTable dt = this.ifLookup.GetSchemaInfo().GetFkInfo();
			if (dt.Rows.Count > 0)
			{
				// for each:
				foreach (DataRow dr in dt.Rows)
				{
					LookupModel rec = new LookupModel();
					rec.Table = dr[0].ToString();
					rec.Field = dr[1].ToString();
					rec.LookupTable = dr[2].ToString();
					rec.LookupField = dr[3].ToString();
					// find an existing lookup. Skip if one already created
					DbView.Resources.LookupInfo.Key key = this.MakeKey(rec.Table, rec.Field);
					if (DbView.Resources.LookupInfo.Instance.FindData(key) == null)
					{
						// find schema of lookup table
						DbView.FieldInfo[] fields = this.ifLookup.GetSchemaInfo().GetSchema(rec.LookupTable);
						// select the first n fields.
						int count = AutoLookupReportFieldMax;
						foreach (DbView.FieldInfo fi in fields)
						{
							rec.SelectedFields.Add(fi.Name);
							if (--count <= 0)
								break;
						}
						DbView.Resources.LookupInfo.Data report = LookupModel.DataTransfer(rec, key);
						DbView.Resources.LookupInfo.Instance.AddLookup(key, report);
					}
				}
				RefreshFromRepository();
				sw.Stop();
				string timerMessage = string.Format("Done in {0}",sw.Elapsed);
				// debug only
				// MessageBox.Show(this, timerMessage, "Create Foreign key lookups", MessageBoxButtons.OK, MessageBoxIcon.Information);
			}
			else
			{
				message = @"No foreign key information was found to create lookups for this connection. You can manually create lookups "+
									"by pressing the [ Add... ] button and following instructions on that form";
				MessageBox.Show(this, message, "Create Foreign key lookups", MessageBoxButtons.OK, MessageBoxIcon.Information);
			}
		}

		// An original Implementation that made a separate database query for the fields of each table was too slow.
		// This version is more complicated and uses a complicated interface call, but gives the desired performance gain
		// by enabling a single database query to fetch all of the information
		private void DoAutoCreateFast()
		{
			// message 
			string message = "This will create lookups based on all the foreign keys that exist in the database. Any existing lookups will not be altered. " + 
							"Do you wish to proceed?";
			if (MessageBox.Show(this, message, "Create Foreign key lookups", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
				// no aborts
				return;
			System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
			sw.Start();

			// use interface to get Fk info
			DataTable dt = this.ifLookup.GetSchemaInfo().GetFkInfo();
			// There may not be any. In this case do nothing, but report why no lookups were created.
			if (dt.Rows.Count > 0)
			{
				// Wait for it
				Cursor.Current = Cursors.WaitCursor;

				// contains a list of lookup tables whose schema we need
				List<string> lookupTables = new List<string>();
				
				// This declares the map that the interface call should return
				Dictionary<string, FieldInfo[]> schemaMap = null;  // assigned later before use
				
				// two pass scan of the Fk information
				// 1. to establish need and record table name
				// 2. make the lookup
				for (int pass = 1; pass <= 2; ++pass)
				{
					foreach (DataRow dr in dt.Rows)
					{
						LookupModel rec = new LookupModel();
						// trust the drivers to populate the table according to spec.
						rec.Table = dr[0].ToString();
						rec.Field = dr[1].ToString();
						rec.LookupTable = dr[2].ToString();
						rec.LookupField = dr[3].ToString();
						
						// find an existing lookup. Don't overwrite anything if one is already created
						// this allows auto-create to add new lookups as FK's are added w/o spoiling customisations
						DbView.Resources.LookupInfo.Key key = this.MakeKey(rec.Table, rec.Field);
						if (DbView.Resources.LookupInfo.Instance.FindData(key) == null)
						{
							// Pass 1: add the table
							if (pass == 1)
							{
								if (!lookupTables.Contains(rec.LookupTable))
									lookupTables.Add(rec.LookupTable);
							}
							// Pass 2: do the biz. This assumes the schema info was obtained after pass 1. (see below)
							else if (pass == 2)
							{
								// find schema of lookup table
								DbView.FieldInfo[] fields = schemaMap[rec.LookupTable]; // can't be missing from the map can it?
								// select the first 4 fields (or whatever value AutoLookupReportFieldMax is set)

								int count = AutoLookupReportFieldMax;
								foreach (DbView.FieldInfo fi in fields)
								{
									rec.SelectedFields.Add(fi.Name);
									if (--count <= 0)
										break;
								}
								// 
								DbView.Resources.LookupInfo.Data report = LookupModel.DataTransfer(rec, key);
								DbView.Resources.LookupInfo.Instance.AddLookup(key, report);
							}
						}
					}
					// On completion of a scan of the fk rows
					if (pass == 1)
					{
						// pass 1: get the fields for the tables identified as lookups so that the report fields can be selected.
						schemaMap = this.ifLookup.GetSchemaInfo().GetBasicSchemas(lookupTables.ToArray());
					}
					else if (pass == 2)
					{
						// pass 2: all done now. Refresh the list.
						RefreshFromRepository();
					}
				}
				// don't boast. remove this when proven.
				sw.Stop();
				string timerMessage = string.Format("Done in {0}",sw.Elapsed);
				// debug only
				// MessageBox.Show(this, timerMessage, "Create Foreign key lookups", MessageBoxButtons.OK, MessageBoxIcon.Information);
				// OK done now
				Cursor.Current = Cursors.Default;
			}
			else
			{
				message = @"No foreign key information was found to create lookups for this connection. You can manually create lookups "+
									"by pressing the [ Add... ] button and following instructions on that form";
				MessageBox.Show(this, message, "Create Foreign key lookups", MessageBoxButtons.OK, MessageBoxIcon.Information);
			}
		}

		private DbView.Resources.LookupInfo.Key MakePartialKey()
		{
			return new DbView.Resources.LookupInfo.Key(this.driverName, this.connectionName, "", "");
		}

		private DbView.Resources.LookupInfo.Key MakeKey(string table, string field)
		{
			return new DbView.Resources.LookupInfo.Key(this.driverName, this.connectionName, table, field);
		}

		private bool TryParseKey(string tableField, out string table, out string field)
		{
			table = field = "";
			// a.b.c  or d.e for schema/no-schema c and e are fields 
			int ipos = tableField.LastIndexOf('.');
			if (ipos <= 0)
				return false;
			table = tableField.Substring(0, ipos);
			field = tableField.Substring(ipos + 1);
			return true;
		}

		private void Help()
		{
			string helpText = "Use this form to add, modify or delete lookup reports for foreign key fields. When a field has a lookup defined " +
							"for it you can right-click on values for that field and lookup the related rows in the connected table. " +
							"\n" +
							"Press [ Add ] to open a form that will allow you to create the first lookup."+
							"";
			this.lblCommand.Text = helpText;
		}
	}
}
